package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.basedoc.model.OrgBuilding;
import com.eling.elcms.basedoc.model.Room;
import com.eling.elcms.basedoc.model.Room.Orientation;
import com.eling.elcms.basedoc.model.Room.Status;
import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.IRoomRegisterDao;
import com.eling.elcms.travel.model.CheckInImplement;
import com.eling.elcms.travel.model.CheckInImplement.DataSource;
import com.eling.elcms.travel.model.RoomRegister;
import com.eling.elcms.travel.model.RoomRegister.RoomStatus;
import com.eling.elcms.travel.model.view.RoomRegisterListView;
import com.eling.elcms.travel.model.view.RoomView;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class RoomRegisterDaoHibernate extends GenericDaoHibernate<RoomRegister, Long> implements IRoomRegisterDao{

	public RoomRegisterDaoHibernate() {
		super(RoomRegister.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Long queryPk(Long pkRoomPersonalInfo) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT  ");
		sb.append("	tr.pkRoomRegister,  ");
		sb.append("tr.pkRoom  ");     
		sb.append("FROM  ");
		sb.append("	travel_roomregister tr  ");
		sb.append("LEFT JOIN travel_roompersonalInfo tr1 ON tr.pkRoomRegister = tr1.pkRoomRegister  ");
		sb.append("WHERE  ");
		sb.append("	tr1.pkRoomPersonalInfo = "+pkRoomPersonalInfo+"  ");
		List<Object[]> list = getSession().createNativeQuery(sb.toString()).list();
		Object[] objects = list.get(0);
		String str = objects[0].toString();
		Long l = Long.parseLong(str);
		return l;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RoomRegister> queryMeter(CheckInImplement checkInImplement) {
		
		StringBuffer sql = new StringBuffer();
		if(DataSource.GroupReserve.equals(checkInImplement.getDataSource())){
			sql.append("   SELECT                                                                                ");
			sql.append("   	br.pkRoom,                                                                           ");
			sql.append("   	br.`code`,                                                                           ");
			sql.append("   	bb.pkOrgBuilding,                                                                    ");
			sql.append("   	bb.`name`,                                                                           ");
			sql.append("   	trr.pkRoomRegister,                                                                  ");
			sql.append("   	trr.electricMeter,                                                                   ");
			sql.append("   	trr.hotWaterMeter,                                                                   ");
			sql.append("   	trr.waterMeter,                                                                      ");
			sql.append("   	trr.checkOutElectricMeter,                                                           ");
			sql.append("   	trr.checkOutHotWaterMeter,                                                           ");
			sql.append("   	trr.checkOutWaterMeter,                                                              ");
			sql.append("   	trr.`roomStatus`,                                                                    ");
			sql.append("   	trr.checkInMeterDate,                                                                ");
			sql.append("   	trr.checkOutMeterDate                                                                ");
			sql.append("   FROM                                                                                  ");
			sql.append("   	travel_roomregister trr                                                              ");
			sql.append("   LEFT JOIN travel_roompersonalInfo trpi ON trpi.pkRoomRegister = trr.pkRoomRegister  ");
			sql.append("   LEFT JOIN travel_checkinimplement tci ON tci.pkCIImplement = trpi.pkCIImplement      ");
			sql.append("   LEFT JOIN bd_room br ON br.pkRoom = trr.pkRoom                                        ");
			sql.append("   LEFT JOIN bd_building bb ON bb.pkOrgBuilding = br.pkOrgBuilding                       ");
			sql.append("   WHERE                                                                                 ");
			sql.append("   	tci.pkCIImplement = :pkCheckInImplement                                              ");
			sql.append("   GROUP BY trr.pkRoomRegister                                              ");
		}
		if(DataSource.NonMember.equals(checkInImplement.getDataSource())){
			sql.append("  SELECT                                                                       ");
			sql.append("  		br.pkRoom,                                                             ");        
			sql.append("    	br.`code`,                                                             ");            
			sql.append("    	bb.pkOrgBuilding,                                                      ");            
			sql.append("    	bb.`name`,                                                             ");            
			sql.append("    	trr.pkRoomRegister,                                                    ");            
			sql.append("    	trr.electricMeter,                                                     ");            
			sql.append("    	trr.hotWaterMeter,                                                     ");            
			sql.append("    	trr.waterMeter,                                                        ");            
			sql.append("    	trr.checkOutElectricMeter,                                             ");            
			sql.append("    	trr.checkOutHotWaterMeter,                                             ");            
			sql.append("    	trr.checkOutWaterMeter,                                                ");  
			sql.append("   		trr.`roomStatus`,                                                      ");
			sql.append("   		trr.checkInMeterDate,                                                  ");
			sql.append("   		trr.checkOutMeterDate                                                  ");
			sql.append("   FROM                                                                        ");
			sql.append("  travel_roomregister trr                                                      ");
			sql.append("  JOIN travel_roompersonalInfo trp ON trp.pkRoomRegister=trr.pkRoomRegister    ");
			sql.append("  JOIN travel_checkinimplement tci on tci.pkCIImplement=trp.pkCIImplement      ");
			sql.append("  JOIN bd_room br ON br.pkRoom=trr.pkRoom                                      ");
			sql.append("  JOIN bd_building bb ON bb.pkOrgBuilding = br.pkOrgBuilding                   ");
			sql.append("  WHERE tci.pkCIImplement = :pkCheckInImplement                                ");
			sql.append("  GROUP BY br.pkRoom                                                           ");
		}
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("pkCheckInImplement", checkInImplement.getPkCIImplement()).list();
		List<RoomRegister> roomList = new ArrayList<>();
		for (int i = 0; i < objList.size(); i++) {
			RoomRegister roomRegister = new RoomRegister();
			Object[] o = objList.get(i);
			Room room = new Room();
			room.setPkRoom(o[0]==null?0:Long.parseLong(o[0].toString()));
			room.setCode(o[1]==null?"":o[1].toString());
			OrgBuilding building = new OrgBuilding();
			building.setPkOrgBuilding(o[2]==null?0:Long.parseLong(o[2].toString()));
			building.setName(o[3]==null?"":o[3].toString());
			room.setOrgBuilding(building);
			roomRegister.setRoom(room);
			roomRegister.setPkRoomRegister(Long.parseLong(o[4].toString()));
			roomRegister.setElectricMeter(o[5]==null?null:Double.parseDouble(o[5].toString()));
			roomRegister.setWaterMeter(o[7]==null?null:Double.parseDouble(o[7].toString()));
			roomRegister.setHotWaterMeter(o[6]==null?null:Double.parseDouble(o[6].toString()));
			roomRegister.setCheckOutElectricMeter(o[8]==null?null:Double.parseDouble(o[8].toString()));
			roomRegister.setCheckOutWaterMeter(o[10]==null?null:Double.parseDouble(o[10].toString()));
			roomRegister.setCheckOutHotWaterMeter(o[9]==null?null:Double.parseDouble(o[9].toString()));
			if(o[11]!=null && o[11].toString().equals(RoomStatus.finish.toString())){
				roomRegister.setRoomStatus(RoomStatus.finish);
			}else if (o[11]!=null && o[11].toString().equals(RoomStatus.CheckIn.toString())) {
				roomRegister.setRoomStatus(RoomStatus.CheckIn);
			}else {
				roomRegister.setRoomStatus(RoomStatus.NoCheckIn);
			}
			roomRegister.setCheckInMeterDate((Date) o[12]);
			roomRegister.setCheckOutMeterDate((Date) o[13]);
			roomList.add(roomRegister);
		}
		return roomList;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<RoomRegisterListView> printRoom(Long pkServicePoint) {
		
		
		StringBuffer sql = new StringBuffer();
		sql.append("  SELECT ");
		sql.append("  bud.`name` budName,rm.`code`, rm.status,rmt.name rmtName FROM bd_building bud ");
		sql.append("  INNER JOIN bd_room rm ON rm.pkOrgBuilding = bud.pkOrgBuilding INNER JOIN travel_roomtype rmt ON rmt.pkRoomType= rm.pkRoomType WHERE bud.pkServicePoint ='"+pkServicePoint+"' ");
		
		List<Object[]> objects = getSession().createNativeQuery(sql.toString()).list();
		
		List<RoomRegisterListView> registerListViews = new ArrayList<>();
		
		for (Object[] obj : objects) {
			RoomRegisterListView reg = new RoomRegisterListView();
			reg.setBuding(obj[0].toString());
			reg.setCode(obj[1].toString());
			reg.setStatus(obj[2].toString());
			reg.setRoomTypeName(obj[3].toString());
			registerListViews.add(reg);
		}
		
		//散客
		StringBuffer sqlNon = new StringBuffer();
		sqlNon.append("  SELECT ");
		sqlNon.append(" rm.`code`,per.`name`perName,non.checkInDate,non.checkOutDate,non.description,per.phone  ");
		sqlNon.append(" FROM travel_checkin cn  ");
		sqlNon.append(" INNER JOIN travel_roomregister roomg ON roomg.pkRoomRegister = cn.pkRoomRegister  ");
		sqlNon.append(" INNER JOIN travel_roompersonalInfo roomp  ON roomp.pkRoomRegister = roomg.pkRoomRegister  ");
		sqlNon.append(" INNER JOIN travel_checkinimplement chem ON chem.pkCIImplement = roomp.pkCIImplement  ");
		sqlNon.append(" INNER JOIN travel_nonmemgroupreserve nonm ON nonm.pkNonMemGroupReserve = chem.pkNonMemGroupReserve  ");
		sqlNon.append(" INNER JOIN travel_nonmembergroup non ON non.pkNonMemberGroup = nonm.pkNonMemberGroup  ");
		sqlNon.append(" INNER JOIN bd_personalinfo per ON per.pkPersonalInfo= roomp.pkPersonalInfo  ");
		sqlNon.append(" INNER JOIN bd_room rm ON rm.pkRoom= roomg.pkRoom  ");
//		sqlNon.append(" INNER JOIN travel_roomtype rmt ON rmt.pkRoomType= rm.pkRoomType  ");
		sqlNon.append(" INNER JOIN bd_building bud ON bud.pkOrgBuilding =rm.pkOrgBuilding  ");
		sqlNon.append(" WHERE  bud.pkServicePoint='"+pkServicePoint+"' ");
		List<Object[]> objNon = getSession().createNativeQuery(sqlNon.toString()).list();
		
		for (RoomRegisterListView regw : registerListViews) {
			for (Object[] obj : objNon) {
				if(regw.getCode().equals(obj[0].toString()) && regw.getStatus()!="Dirty"){
					regw.setPerName(obj[1].toString());
					regw.setCheckInDate((Date)obj[2]);
					regw.setCheckOutDate((Date)obj[3]);
					regw.setDescription(obj[4]==null?"":obj[4].toString());
					regw.setPhone(obj[5]==null?"":obj[5].toString());
				}
			}
		}
		
		StringBuffer sqlGrp = new StringBuffer();
		sqlGrp.append(" SELECT  ");
		sqlGrp.append(" rm.`code`,per.`name`perName,gru.checkInDate,gru.checkOutDate,gru.description,per.phone  ");
		sqlGrp.append(" FROM travel_checkin cn  ");
		sqlGrp.append(" INNER JOIN travel_roomregister roomg ON roomg.pkRoomRegister = cn.pkRoomRegister  ");
		sqlGrp.append(" INNER JOIN travel_roompersonalInfo roomp  ON roomp.pkRoomRegister = roomg.pkRoomRegister  ");
		sqlGrp.append(" INNER JOIN travel_checkinimplement chem ON chem.pkCIImplement = roomp.pkCIImplement  ");
		sqlGrp.append(" INNER JOIN travel_groupreserve gru ON gru.pkGroupReserve = chem.pkGroupReserve  ");
		sqlGrp.append(" INNER JOIN bd_personalinfo per ON per.pkPersonalInfo= roomp.pkPersonalInfo  ");
		sqlGrp.append(" INNER JOIN bd_room rm ON rm.pkRoom= roomg.pkRoom  ");
//		sqlGrp.append(" INNER JOIN travel_roomtype rmt ON rmt.pkRoomType= rm.pkRoomType  ");
		sqlGrp.append(" INNER JOIN bd_building bud ON bud.pkOrgBuilding =rm.pkOrgBuilding  ");
		sqlGrp.append(" WHERE  bud.pkServicePoint='"+pkServicePoint+"' ");
		List<Object[]> objGrp = getSession().createNativeQuery(sqlGrp.toString()).list();
		for (Object[] obj : objGrp) {
			
			for (RoomRegisterListView rg : registerListViews) {
				if(rg.getCode().equals(obj[0].toString()) && rg.getStatus()!="Dirty"){
					rg.setPerName(obj[1].toString());
					rg.setCheckInDate((Date)obj[2]);
					rg.setCheckOutDate((Date)obj[3]);
					rg.setDescription(obj[4]==null?"":obj[4].toString());
					rg.setPhone(obj[5]==null?"":obj[5].toString());
				}
			}
		}
		return registerListViews;
	}

	@SuppressWarnings("unchecked")
	@Override
	public RoomView queryByPkRoom(Long pkRoom) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT rm.`code`,bud.`name`,rm.floors,rm.orientation,rm.acreage,rm.bedcount,rm.`status`, cim.statementStatus,cim.dataSource,rmt.name ramtName,cim.pkCIImplement,cin.checkOutStatus   ");
		sql.append(" FROM travel_roomregister rmr                                                                                                           ");
		sql.append(" INNER JOIN bd_room rm ON rm.pkRoom = rmr.pkRoom   INNER JOIN travel_roomtype rmt ON rmt.pkRoomType = rm.pkRoomType 			   ");
		sql.append(" INNER JOIN bd_building bud ON bud.pkOrgBuilding = rm.pkOrgBuilding                                                                ");
		sql.append(" INNER JOIN travel_roompersonalInfo rmp ON rmp.pkRoomRegister =  rmr.pkRoomRegister                                                ");
		sql.append(" INNER JOIN travel_checkinimplement cim ON cim.pkCIImplement = rmp.pkCIImplement                                                    ");
		sql.append(" INNER JOIN travel_checkin cin ON cin.pkCheckInImplement = cim.pkCIImplement                                                     ");
		sql.append(" WHERE rm.pkRoom='"+pkRoom+"'                                                                                                             ");
		List<Object[]> objects = getSession().createNativeQuery(sql.toString()).list();
//		List<RoomView> roomViews = new ArrayList<>(); 
		RoomView rv = new RoomView();
		for (Object[] obj : objects) {
			rv.setCode(obj[0].toString());
			rv.setBudName(obj[1].toString());
			rv.setFloors(new Integer(obj[2].toString()));
			if(obj[3].toString().equals(Orientation.East.name())){
				rv.setOrientation("东");
			} else if(obj[3].toString().equals(Orientation.South.name())){
				rv.setOrientation("南");
			}else if(obj[3].toString().equals(Orientation.West.name())){
				rv.setOrientation("西");
			}else if(obj[3].toString().equals(Orientation.North.name())){
				rv.setOrientation("北");
			}else if(obj[3].toString().equals(Orientation.EastNorth.name())){
				rv.setOrientation("东北");
			}else if(obj[3].toString().equals(Orientation.WestSouth.name())){
				rv.setOrientation("西南");
			}else if(obj[3].toString().equals(Orientation.WestNorth.name())){
				rv.setOrientation("西北");
			}else if(obj[3].toString().equals(Orientation.EastSouth.name())){
				rv.setOrientation("东南");
			}
			rv.setAcreage(new Double(obj[4].toString()));
			rv.setBedcount(obj[5]==null?0:new Integer(obj[5].toString()));
			if(obj[6].toString().equals(Status.Clear.name())){
				rv.setrStatus("净房");
			}else if(obj[6].toString().equals(Status.Dirty.name())){
				rv.setrStatus("脏房");
			}else if(obj[6].toString().equals(Status.CheckIn.name())){
				rv.setrStatus("在住房");
			}else if(obj[6].toString().equals(Status.Repairing.name())){
				rv.setrStatus("维修房");
			}else if(obj[6].toString().equals(Status.Temporary.name())){
				rv.setrStatus("临时房");
			}else if(obj[6].toString().equals(Status.SelfUse.name())){
				rv.setrStatus("自用房");
			}else if(obj[6].toString().equals(Status.WillGo.name())){
				rv.setrStatus("将走房");
			}else if(obj[6].toString().equals(Status.Appoint.name())){
				rv.setrStatus("预定房");
			}else if(obj[6].toString().equals(Status.Free.name())){
				rv.setrStatus("免费房");
			}
			rv.setStatementStatus(obj[7].toString());
			rv.setDataSource(obj[8].toString());
			rv.setRmtName(obj[9].toString());
			rv.setPkCIImplement(new Long(obj[10].toString()));
			rv.setCheckOutStatus(obj[11].toString());
//			roomViews.add(rv);
		}
		return rv;
	}
}
